package com.mamesoft.namibiamc.beans;

import com.mamesoft.namibiamc.tools.JDBC;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import hirondelle.date4j.DateTime;
import java.io.Serializable;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

public class PazientiACBean implements Serializable {

    private int medicoId;
    private int id;
    private String nome;
    private String cognome;
    private String dataNascita;

    public List<PazientiACBean> autocompletePaziente(Object event) throws SQLException {
        String pref = (event != null) ? event.toString() : "";
        pref.replace("'", "");
        LinkedList<PazientiACBean> lP = new LinkedList<PazientiACBean>();
        String query = "SELECT * FROM anagrafe LEFT JOIN paziente ON ( anagrafe.ID = paziente.ID ) "
                + "LEFT JOIN medico ON ( anagrafe.ID = medico.ID ) "
                + "LEFT JOIN operatore ON ( anagrafe.ID = operatore.ID ) "
                + "WHERE (anagrafe.cognome LIKE '"+pref+"%' OR "
                + "anagrafe.nome LIKE '"+pref+"%') "
                + "AND paziente.ID IS NULL "
                + "AND medico.ID IS NULL "
                + "AND operatore.ID IS NULL";
        ResultSet set = null;
        Connection connect = null;

        try {
            connect = JDBC.connect();
            set = JDBC.executeQuery(query, connect);

            while (set.next()) {
                PazientiACBean pB = new PazientiACBean();
                pB.setNome(set.getString("nome"));
                pB.setCognome(set.getString("cognome"));
                pB.setId(set.getInt("ID"));
                Timestamp t = set.getTimestamp("data_nascita");
                DateTime f = new DateTime(t.toString());
                pB.setDataNascita(f.format("DD/MM/YYYY"));
                lP.add(pB);
            }

        } catch (Exception ex) {
            throw new SQLException(ex);
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
                if (set != null) {
                    set.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex);
            }
        }
        return lP;
    }

    public List<PazientiACBean> autocompleteListaPazienti(Object event) throws SQLException {
        String pref = (event != null) ? event.toString() : "";
        pref.replace("'", "");
        LinkedList<PazientiACBean> lP = new LinkedList<PazientiACBean>();
        String query = "SELECT nome,cognome,data_nascita,paziente.id "
                + " FROM anagrafe"
                + " JOIN paziente ON anagrafe.id = paziente.id"
                + " JOIN medico on paziente.medico = medico.id WHERE medico.id ='" + medicoId + "' "
                + "AND (anagrafe.cognome LIKE '"+pref+"%' OR "
                + "anagrafe.nome LIKE '"+pref+"%');";
        ResultSet set = null;
        Connection connect = null;

        try {
            connect = JDBC.connect();
            set = JDBC.executeQuery(query, connect);

            while (set.next()) {
                PazientiACBean pB = new PazientiACBean();
                pB.setNome(set.getString("nome"));
                pB.setCognome(set.getString("cognome"));
                pB.setId(set.getInt("paziente.id"));
                Timestamp t = set.getTimestamp("data_nascita");
                DateTime f = new DateTime(t.toString());
                pB.setDataNascita(f.format("DD/MM/YYYY"));
                lP.add(pB);
            }

        } catch (Exception ex) {
            throw new SQLException(ex);
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
                if (set != null) {
                    set.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex);
            }
        }
        if (lP.isEmpty()) {
            PazientiACBean pB = new PazientiACBean();
            pB.setNome("Non hai pazienti, aggiungine uno");         
            lP.add(pB);
        }
        return lP;
    }

    public List<PazientiACBean> autocompleteGenitore(Object event) throws SQLException {
        String pref = (event != null) ? event.toString() : "";
        pref.replace("'", "");
        LinkedList<PazientiACBean> lP = new LinkedList<PazientiACBean>();
        String query = "SELECT * FROM anagrafe LEFT JOIN paziente ON ( anagrafe.ID = paziente.ID ) "
                + "WHERE (anagrafe.cognome LIKE '"+pref+"%' OR "
                + "anagrafe.nome LIKE '"+pref+"%') AND anagrafe.id != '" + getId() + "' ";
        // + "AND paziente.ID != '"+paz+"' ";
        ResultSet set = null;
        Connection connect = null;
        PazientiACBean p = new PazientiACBean();
        p.setNome("ORFANO");
        p.setCognome("");
        p.setId(0);
        lP.add(p);
        try {
            connect = JDBC.connect();
            set = JDBC.executeQuery(query, connect);

            while (set.next()) {
                PazientiACBean pB = new PazientiACBean();
                pB.setNome(set.getString("nome"));
                pB.setCognome(set.getString("cognome"));
                pB.setId(set.getInt("ID"));
                Timestamp t = set.getTimestamp("data_nascita");
                DateTime f = new DateTime(t.toString());
                pB.setDataNascita(f.format("DD/MM/YYYY"));
                lP.add(pB);
            }

        } catch (Exception ex) {
            throw new SQLException(ex);
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
                if (set != null) {
                    set.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex);
            }
        }
        return lP;
    }

    public List<PazientiACBean> autocompleteCapo(Object event) throws SQLException {
        String pref = (event != null) ? event.toString() : "";
        pref.replace("'", "");
        LinkedList<PazientiACBean> lP = new LinkedList<PazientiACBean>();
        PazientiACBean p = new PazientiACBean();
        p.setNome("CAPO VILLAGGIO");
        p.setCognome("");
        p.setId(0);
        lP.add(p);
        String query = "SELECT * FROM anagrafe LEFT JOIN paziente ON ( anagrafe.ID = paziente.ID ) "
                + "WHERE (anagrafe.cognome LIKE '"+pref+"%' OR "
                + "anagrafe.nome LIKE '"+pref+"%') "
                + "AND paziente.ID IS NOT NULL "
                + "AND paziente.capo_villaggio IS NULL";
        ResultSet set = null;
        Connection connect = null;

        try {
            connect = JDBC.connect();
            set = JDBC.executeQuery(query, connect);

            while (set.next()) {
                PazientiACBean pB = new PazientiACBean();
                pB.setNome(set.getString("nome"));
                pB.setCognome(set.getString("cognome"));
                pB.setId(set.getInt("ID"));
                Timestamp t = set.getTimestamp("data_nascita");
                DateTime f = new DateTime(t.toString());
                pB.setDataNascita(f.format("DD/MM/YYYY"));
                lP.add(pB);
            }

        } catch (Exception ex) {
            throw new SQLException(ex);
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
                if (set != null) {
                    set.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex);
            }
        }
        return lP;
    }

    public List<PazientiACBean> autocompleteMedico(Object event) throws SQLException {
        String pref = (event != null) ? event.toString() : "";
        pref.replace("'", "");
        LinkedList<PazientiACBean> lP = new LinkedList<PazientiACBean>();
        String query = "SELECT * FROM anagrafe RIGHT JOIN medico ON ( anagrafe.ID = medico.ID ) "
                + "WHERE (anagrafe.cognome LIKE '"+pref+"%' OR "
                + "anagrafe.nome LIKE '"+pref+"%') ";
        ResultSet set = null;
        Connection connect = null;
        try {
            connect = JDBC.connect();
            set = JDBC.executeQuery(query, connect);

            while (set.next()) {
                PazientiACBean pB = new PazientiACBean();
                pB.setNome(set.getString("nome"));
                pB.setCognome(set.getString("cognome"));
                pB.setId(set.getInt("ID"));
                Timestamp t = set.getTimestamp("data_nascita");
                DateTime f = new DateTime(t.toString());
                pB.setDataNascita(f.format("DD/MM/YYYY"));
                lP.add(pB);
            }

        } catch (Exception ex) {
            throw new SQLException(ex);
        } finally {
            try {
                if (connect != null) {
                    connect.close();
                }
                if (set != null) {
                    set.close();
                }
            } catch (SQLException ex) {
                throw new SQLException(ex);
            }
        }
        return lP;
    }

    /**
     * @return the id
     */
    public int getId() {
        return id;
    }

    /**
     * @param id the id to set
     */
    public void setId(int id) {
        this.id = id;
    }

    /**
     * @return the nome
     */
    public String getNome() {
        return nome;
    }

    /**
     * @param nome the nome to set
     */
    public void setNome(String nome) {
        this.nome = nome;
    }

    /**
     * @return the cognome
     */
    public String getCognome() {
        return cognome;
    }

    /**
     * @param cognome the cognome to set
     */
    public void setCognome(String cognome) {
        this.cognome = cognome;
    }

    /**
     * @return the dataNascita
     */
    public String getDataNascita() {
        return dataNascita;
    }

    /**
     * @param dataNascita the dataNascita to set
     */
    public void setDataNascita(String dataNascita) {
        this.dataNascita = dataNascita;
    }

    /**
     * @return the medicoId
     */
    public int getMedicoId() {
        return medicoId;
    }

    /**
     * @param medicoId the medicoId to set
     */
    public void setMedicoId(int medicoId) {
        this.medicoId = medicoId;
    }
}
